
use "$dataPath/MSRB/school_yields_20200821.dta" , clear
drop if year(trade_date) < 2001 | year(trade_date) > 2017

sort state
g alphacode=state
merge m:1 alphacode using "$dataPath/state_code_lookup.dta", keepusing(numericcode)
drop if _merge==2
drop _merge alphacode
rename numericcode statefp

sort district_name
merge m:1 district_name statefp using "$dataPath/District_SLRexposure/districtlevel_exposure_fullname_9_13_21.dta"
tab _merge
keep if _merge == 3
drop _merge

sort fips_use
merge m:1 fips_use using "$dataPath/fips_density.dta"
tab _merge
keep if _merge == 3
drop _merge

merge m:1 fips_use name using "$dataPath/worried_state_bea.dta", force
tab _merge
keep if _merge == 3
drop _merge

rename geoid geoid2
gen geoid = real(geoid2)

sort name geoid year
merge m:1 name geoid year using "$dataPath/median_price_district.dta"
tab _merge
drop if _merge==2
drop _merge

g ymon=mofd(trade_date)
sort name statefp ymon

merge m:1 district_name statefp ymon using "$dataPath/median_price_exposed.dta"
tab _merge
drop if _merge==2
drop _merge

merge m:1 district_name statefp year using "$dataPath/median_price_exposed_annual.dta"
tab _merge
drop if _merge==2
drop _merge

merge m:1 name geoid  using "$dataPath/coastal_distance.dta"
tab _merge
drop if _merge==2
drop _merge

*** Add in the zip code characteristics merge 
sort name fips_use year
merge m:1 name fips_use year  using "$dataPath/district_zip.dta"
tab _merge
drop if _merge==2
drop _merge

gen date = mofd(trade_date)
merge m:1 name fips_use date  using "$dataPath//district_hp_wage.dta"
tab _merge
drop if _merge==2
drop _merge

merge m:1 state  using "$dataPath/stateproptax.dta"
tab _merge
drop if _merge==2
drop _merge

center rate_p, g(rate_std) standardize
center local_pct, g(local_std) standardize

**************** House price data merge and variable construction
merge m:1 district_name statefp year using "$dataPath/reg_price_index.dta"
drop if _merge==2
drop _merge


*deal with missing price indices:
replace med_price_exp=1 if med_price_exp==.
replace med_price_exp_yr=1 if med_price_exp_yr==.
replace avg_price_exp=1 if avg_price_exp==.
replace avg_price_exp_yr=1 if avg_price_exp_yr==.
g log_median_hp=log(median_price)

gen log_med_pr_exp = log(med_price_exp)
gen log_med_pr_noexp = log(med_price_noexp)

gen log_med_pr_exp_yr = log(med_price_exp_yr)
gen log_med_pr_noexp_yr = log(med_price_noexp_yr)

gen log_avg_pr_exp_yr = log(avg_price_exp_yr)
gen log_avg_pr_noexp_yr = log(avg_price_noexp_yr)

gen log_med_pr_adj_exp_yr = log(med_price_exp_yr*percent_exposed_6+1)
gen log_med_pr_adj_noexp_yr = log(med_price_noexp_yr)
ren year_dist reg_price_index
label var log_med_pr_noexp_yr "Med Price NEX"
label var log_med_pr_exp_yr "Med Price EX"
label var log_med_pr_adj_exp_yr "Med Price EX s"
label var reg_price_index "Price Index"



**** PGP House Price data ***
rename district_name districtname
merge m:1 districtname statefp year using "$dataPath/hp_index_fullrange_w_zillow.dta", keepusing(zillow_hp avg_price med_price p10_price p25_price p75_price p90_price transid)
keep if _merge == 3 | _merge == 1
drop _merge
rename districtname district_name 
gen log_zillow_hp = log(zillow_hp)
foreach x of varlist p10_price p25_price med_price p75_price p90_price {
	gen log_`x' = log(`x')
}
label var log_zillow_hp "Log(Median Zillow House Price)"
label var log_p10_price "Log(House Price 10th Percentile)"
label var log_p25_price "Log(House Price 25th Percentile)"
label var log_med_price "Log(House Price 50th Percentile)"
label var log_p75_price "Log(House Price 75th Percentile)"
label var log_p90_price "Log(House Price 90th Percentile)"

****** Generate projection exposure variables

merge m:1 year using "$dataPath/SLR Projections/SLRProjectionsseries.dta", force
tab _merge
keep if _merge == 1 | _merge == 3
drop _merge



*** These measures assign exposure by rounding the projected SLR to the nearest foot and then aplpying that level of exposure to each district. Ranges are based on the difference between the fraction exposed to the high and low projections. 
*** Can use either all studies or the ones Ryan pulled. _r measures are the restricted sample of articles chosen by the method discussed in the paper.


gen med_treat_r=0

replace	med_treat_r=year_med_r*percent_exposed_1		if year==	2000
replace	med_treat_r=year_med_r*percent_exposed_1		if year==	2001
replace	med_treat_r=year_med_r*percent_exposed_1		if year==	2002
replace	med_treat_r=year_med_r*percent_exposed_1		if year==	2003
replace	med_treat_r=year_med_r*percent_exposed_1		if year==	2004
replace	med_treat_r=year_med_r*percent_exposed_1		if year==	2005
replace	med_treat_r=year_med_r*percent_exposed_1		if year==	2006
replace	med_treat_r=year_med_r*percent_exposed_1		if year==	2007
replace	med_treat_r=year_med_r*percent_exposed_2		if year==	2008
replace	med_treat_r=year_med_r*percent_exposed_3		if year==	2009
replace	med_treat_r=year_med_r*percent_exposed_3		if year==	2010
replace	med_treat_r=year_med_r*percent_exposed_3		if year==	2011
replace	med_treat_r=year_med_r*percent_exposed_3		if year==	2012
replace	med_treat_r=year_med_r*percent_exposed_3		if year==	2013
replace	med_treat_r=year_med_r*percent_exposed_2		if year==	2014
replace	med_treat_r=year_med_r*percent_exposed_3		if year==	2015
replace	med_treat_r=year_med_r*percent_exposed_2		if year==	2016
replace	med_treat_r=year_med_r*percent_exposed_2		if year==	2017


gen med_treat=0

replace	med_treat=year_med*percent_exposed_1		if year==	2000
replace	med_treat=year_med*percent_exposed_1		if year==	2001
replace	med_treat=year_med*percent_exposed_1		if year==	2002
replace	med_treat=year_med*percent_exposed_1		if year==	2003
replace	med_treat=year_med*percent_exposed_1		if year==	2004
replace	med_treat=year_med*percent_exposed_1		if year==	2005
replace	med_treat=year_med*percent_exposed_1		if year==	2006
replace	med_treat=year_med*percent_exposed_1		if year==	2007
replace	med_treat=year_med*percent_exposed_2		if year==	2008
replace	med_treat=year_med*percent_exposed_3		if year==	2009
replace	med_treat=year_med*percent_exposed_3		if year==	2010
replace	med_treat=year_med*percent_exposed_3		if year==	2011
replace	med_treat=year_med*percent_exposed_3		if year==	2012
replace	med_treat=year_med*percent_exposed_3		if year==	2013
replace	med_treat=year_med*percent_exposed_2		if year==	2014
replace	med_treat=year_med*percent_exposed_2		if year==	2015
replace	med_treat=year_med*percent_exposed_2		if year==	2016
replace	med_treat=year_med*percent_exposed_2		if year==	2017
  
  
gen range_treat_r=0
  
replace	range_treat_r=year_range_r*(percent_exposed_3-percent_exposed_0)			if year==	2000
replace	range_treat_r=year_range_r*(percent_exposed_3-percent_exposed_0)			if year==	2001
replace	range_treat_r=year_range_r*(percent_exposed_3-percent_exposed_0)			if year==	2002
replace	range_treat_r=year_range_r*(percent_exposed_3-percent_exposed_0)			if year==	2003
replace	range_treat_r=year_range_r*(percent_exposed_3-percent_exposed_0)			if year==	2004
replace	range_treat_r=year_range_r*(percent_exposed_3-percent_exposed_0)			if year==	2005
replace	range_treat_r=year_range_r*(percent_exposed_3-percent_exposed_0)			if year==	2006
replace	range_treat_r=year_range_r*(percent_exposed_2-percent_exposed_1)			if year==	2007
replace	range_treat_r=year_range_r*(percent_exposed_3-percent_exposed_1)			if year==	2008
replace	range_treat_r=year_range_r*(percent_exposed_4-percent_exposed_1)			if year==	2009
replace	range_treat_r=year_range_r*(percent_exposed_5-percent_exposed_1)			if year==	2010
replace	range_treat_r=year_range_r*(percent_exposed_5-percent_exposed_1)			if year==	2011
replace	range_treat_r=year_range_r*(percent_exposed_6-percent_exposed_1)			if year==	2012
replace	range_treat_r=year_range_r*(percent_exposed_5-percent_exposed_1)			if year==	2013
replace	range_treat_r=year_range_r*(percent_exposed_4-percent_exposed_1)			if year==	2014
replace	range_treat_r=year_range_r*(percent_exposed_6-percent_exposed_1)			if year==	2015
replace	range_treat_r=year_range_r*(percent_exposed_6-percent_exposed_1)			if year==	2016
replace	range_treat_r=year_range_r*(percent_exposed_6-percent_exposed_1)			if year==	2017


gen range_treat=0
  
replace	range_treat=year_range*(percent_exposed_3-percent_exposed_0)			if year==	2000
replace	range_treat=year_range*(percent_exposed_3-percent_exposed_0)			if year==	2001
replace	range_treat=year_range*(percent_exposed_3-percent_exposed_0)			if year==	2002
replace	range_treat=year_range*(percent_exposed_3-percent_exposed_0)			if year==	2003
replace	range_treat=year_range*(percent_exposed_3-percent_exposed_0)			if year==	2004
replace	range_treat=year_range*(percent_exposed_3-percent_exposed_0)			if year==	2005
replace	range_treat=year_range*(percent_exposed_3-percent_exposed_0)			if year==	2006
replace	range_treat=year_range*(percent_exposed_2-percent_exposed_0)			if year==	2007
replace	range_treat=year_range*(percent_exposed_3-percent_exposed_0)			if year==	2008
replace	range_treat=year_range*(percent_exposed_5-percent_exposed_0)			if year==	2009
replace	range_treat=year_range*(percent_exposed_5-percent_exposed_0)			if year==	2010
replace	range_treat=year_range*(percent_exposed_5-percent_exposed_0)			if year==	2011
replace	range_treat=year_range*(percent_exposed_5-percent_exposed_0)			if year==	2012
replace	range_treat=year_range*(percent_exposed_5-percent_exposed_0)			if year==	2013
replace	range_treat=year_range*(percent_exposed_4-percent_exposed_0)			if year==	2014
replace	range_treat=year_range*(percent_exposed_5-percent_exposed_0)			if year==	2015
replace	range_treat=year_range*(percent_exposed_5-percent_exposed_0)			if year==	2016
replace	range_treat=year_range*(percent_exposed_4-percent_exposed_0)			if year==	2017
    
  
 



capture drop one-var_6
g myear =year
replace myear=2001 if year<=2001
replace myear=2017 if year>2017
capture drop _merge
merge m:1 myear using "$dataPath/Variance.dta"
drop myear _merge

gen mean_exp_n=(one_n+two_n+three_n+four_n+five_n+six_n)*percent_exposed_1+(two_n+three_n+four_n+five_n+six_n)*percent_exposed_2+(three_n+four_n+five_n+six_n)*percent_exposed_3+(four_n+five_n+six_n)*percent_exposed_4+(five_n+six_n)*percent_exposed_5+six_n*percent_exposed_6

g var_6=pct_over_6*percent_exposed_6
 
sort fips_use
merge m:1 fips_use using "$dataPath/bea_county.dta"
tab _merge
keep if _merge == 3
drop _merge

egen bond_num = group(cusip)
egen dist_num = group(district_name)
egen state_num = group(state)

gen ym_date = ym(year(trade_date), month(trade_date))
format ym_date %tm

gen offer_ym_date = ym(year(offering_date), month(offering_date))
gen offer_year = year(offering_date)
format offer_ym_date %tm

* control variables
gen log_daily_volume = log(daily_volume)
gen log_monthly_volume = log(monthly_volume)
gen log_issue_amt = log(total_maturity_offering_amt)
gen log_average_value = log(average_value)

egen sec_type = group(security_code)

gen eastcoast = 0
replace eastcoast = 1 if state == "NJ" | state == "NY" | state == "MA" | state == "SC" | state == "CT" | state == "NC" | state == "FL" | state == "ME" | state == "GA" | state == "RI" | state == "MD" | state == "VA"

gen gulfcoast = 0
replace gulfcoast = 1 if state == "LA" | state == "MS" | state == "AL" | state == "TX" 

gen westcoast = 0
replace westcoast = 1 if state == "CA" | state == "WA" | state == "OR"

* measures of dispersion in transaction data
sort bond_num trade_date
by bond_num trade_date: egen price_day_stdev = sd(price)
by bond_num trade_date: egen price_max = max(price)
by bond_num trade_date: egen price_min = min(price)
gen price_day_range = (price_max - price_min)/price_dvw
drop price_max price_min

sort bond_num year_month trade_date
by bond_num year_month: egen price_month_stdev = sd(price)
by bond_num year_month: egen price_max = max(price)
by bond_num year_month: egen price_min = min(price)
gen price_month_range = (price_max - price_min)/price_mvw
drop price_max price_min

***** Generate the exposure to inland water
gen percentwater=(Water_Area) / (Water_Area+Land_Area)
gen water=(percentwater>.1)

* variables for regressions
gen pctexposed = percent_exposed_6

*** Create stanardized measure for exposure and storm surge
center stormsurge, g(stormsurge_std) standardize
center percent_exposed_6, g(pct_std) standardize

label var pctexposed "Frac. Exposed"
label var pct_std "Frac. Exposed (Std)"

gen log_maturity = log(time_to_mat)
gen longm = (time_to_mat ~= . & time_to_mat > 10)

gen ave_state_worried_2014_nonorm = ave_state_worried_2014
egen ave_state_worried_2014b = std(ave_state_worried_2014)
egen worried_county=std(worried_2014)
replace ave_state_worried_2014= ave_state_worried_2014b
label var ave_state_worried_2014 "State Worry"
label var ave_state_worried_2014_nonorm "State Worry"
label var worried_county "County Worry"

gen worry = (ave_state_worried_2014 ~= . & ave_state_worried_2014 > -0.75) /* two groups of states */
/** Picking this to split on the median for the eastgulf (bond weighted) **/
gen worry2 = (ave_state_worried_2014 ~= . & ave_state_worried_2014 >= -1.275) /* two groups of states */

gen addl_credit_dum = (additional_credit_flag == "Y")

egen insurer_num = group(bond_insurance_code)
sum insurer_num
replace insurer_num = `r(max)' + 1 if bond_insurance_code == ""

gen genob_dum = (security_code == "K" | security_code == "D")
gen non_go_dum = 1 - genob_dum
egen security_num = group(security_code)

* unique bond-month observations and data quality restrictions
sort bond_num ym_date
keep if ~(bond_num == bond_num[_n+1] & ym_date == ym_date[_n+1])
gen bond_age_filter =  bond_age > 0.25
if $bond_age == 1 {
	keep if mma_spread_mvw ~= . & pctexposed ~= . & bond_age > 0.25
	}
else {
	keep if mma_spread_mvw ~= . & pctexposed ~= . 
	}

sort state year bond_num
by state year: egen state_year_count = count(bond_num)
gen wgt = 1/state_year_count


*** Paul checks balance ***
preserve
collapse (count) num_obs=bond_num, by(fips dist_num year)
gen has_obs = 1
collapse (sum) has_obs, by(fips dist_num)
gen balanced = has_obs == 17
egen num_dist = count(dist_num), by(fips)
tempfile dist_obs
save `dist_obs'
restore
merge m:1 dist_num fips using `dist_obs', nogen

gen post = (year >= 2013)
label var post "Post"


/* appendix table on effects of sample restriction - balanced panel of districts by year */
tab year

tab state

sort bond_num ym_date
tab state if bond_num ~= bond_num[_n-1]

sort dist_num bond_num ym_date
tab state if dist_num ~= dist_num[_n-1]

sort year bond_num trade_date
gen temp1 = total_maturity_offering_amt/1000000 if ~(year == year[_n-1] & bond_num == bond_num[_n-1])
by year: egen total_face_all = sum(temp1)
gen temp2 = total_maturity_offering_amt/1000000 if ~(year == year[_n-1] & bond_num == bond_num[_n-1]) & state != "CA"
by year: egen total_face_all_eg = sum(temp2)
drop temp*


* require at least two districts per county
keep if num_dist > 1

tab year

tab state

sort bond_num ym_date
tab state if bond_num ~= bond_num[_n-1]

sort dist_num bond_num ym_date
tab state if dist_num ~= dist_num[_n-1]

sort year bond_num trade_date
gen temp1 = total_maturity_offering_amt/1000000 if ~(year == year[_n-1] & bond_num == bond_num[_n-1])
by year: egen total_face_multi = sum(temp1)
gen temp2 = total_maturity_offering_amt/1000000 if ~(year == year[_n-1] & bond_num == bond_num[_n-1]) & state != "CA"
by year: egen total_face_multi_eg = sum(temp2)
drop temp*


* require one observation per district-year

if "$isbal"=="balanced" {
	keep if balanced == 1
	}

tab year

tab state

sort bond_num ym_date
tab state if bond_num ~= bond_num[_n-1]

sort dist_num bond_num ym_date
tab state if dist_num ~= dist_num[_n-1]

sort year bond_num trade_date
gen temp1 = total_maturity_offering_amt/1000000 if ~(year == year[_n-1] & bond_num == bond_num[_n-1])
by year: egen total_face_balanced = sum(temp1)
gen temp2 = total_maturity_offering_amt/1000000 if ~(year == year[_n-1] & bond_num == bond_num[_n-1]) & state != "CA"
by year: egen total_face_${isbal}_eg = sum(temp2)
drop temp*

tabstat total_face_all total_face_multi total_face_balanced, by(year)

tabstat total_face_all_eg total_face_multi_eg total_face_${isbal}_eg, by(year)


* exclude California from the sample
preserve
keep if state != "CA"

tab year

tab state

sort bond_num ym_date
tab state if bond_num ~= bond_num[_n-1]

sort dist_num bond_num ym_date
tab state if dist_num ~= dist_num[_n-1]

* number of observations per district-month (for identification)
sort fips ym_date dist_num cusip
gen temp = 1 if ~(fips == fips[_n-1] & ym_date == ym_date[_n-1] & dist_num == dist_num[_n-1])
by fips ym_date: egen num_dists_county_ym = sum(temp)
drop temp

sum num_dists_county_ym if ~(fips == fips[_n-1] & ym_date == ym_date[_n-1]), detail

restore


* winsorize outcome variables
winsor yield_sas, generate(yield_sas_w) p(0.01)
winsor yield_dvw, generate(yield_dvw_w) p(0.01)
winsor yield_mvw, generate(yield_mvw_w) p(0.01)
winsor mma_spread_mvw, generate(mma_spread_mvw_w) p(0.01)
*winsor swap_spread_mvw, generate(swap_spread_mvw_w) p(0.01)
winsor log_daily_volume, generate(log_daily_volume_w) p(0.01)
winsor log_monthly_volume, generate(log_monthly_volume_w) p(0.01)

* new variables to address referee concerns
sum liq_*

winsor liq_amihud_mvw, gen(liq_amihud_w) p(0.01)
winsor liq_roundtrip_mvw, gen(liq_roundtrip_w) p(0.01)
winsor liq_dispersion_mvw, gen(liq_dispersion_w) p(0.01)
winsor liq_turnover_mvw, gen(liq_turnover_w) p(0.01)

winsor stormsurge_std, gen(stormsurge_std_w) p(0.01)

* variable names
label var yield_mvw_w "Yield-to-Maturity (\%)"
label var mma_bench "MMA AAA-Rated Tax-Exempt Rate (\%)"
label var mma_spread_mvw_w "Spread over MMA Curve (bps)"

label var time_to_mat "Time to Maturity"
label var log_average_value "Log(Average Value)"
label var coupon "Coupon"
label var bond_age "Bond Age"
label var monthly_volume "Monthly Trading Volume (\$000s)"
label var log_monthly_volume "Log(Monthly Volume)"
label var liq_turnover_w "Monthly Turnover"
label var price_month_stdev "Monthly S.D. of Price (per \$100)"
label var callable_dum "Callable"
label var insured_dum "Insured"
label var genob_dum "General Obligation"
label var irs_avg_income "Residents' Average Income (\$000s)"
label var average_value "Average Price, Single Family Home (\$000s)"
replace monthly_volume=monthly_volume/1000
label var percent_exposed_5 "Fraction of Properties Exposed (5 foot SLR)"
label var percent_exposed_6 "Fraction of Properties Exposed (6 foot SLR)"
label var  stormsurge_std_w "Storm Surge Exposure"
label var  post "Post"

gen eastgulf=eastcoast+gulfcoast

g dummy=1

/*** setup complete ***/

